﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.IO;

namespace SimpleHttpServer.GuestBook
{
    public class SqlLiteGuestBook : IGuestBook, IDisposable
    {
        private SQLiteConnection _conn;
        private bool _disposed;

        public SqlLiteGuestBook()
        {
            string dbPath = Path.Combine(CommonHelper.AppPath, "GuestBook.db");

            _conn = new SQLiteConnection(string.Format("data source={0};", dbPath));
            _conn.Open();
        }


        private User AddUser(string userName)
        {

            string findQuery = "select id, name from Users where name=@name LIMIT 1";
            string insertQuery = "insert into Users(name) values (@name)";
            string getLastGeneratedID = "select last_insert_rowid()";


            SQLiteTransaction tran = null;

            try
            {
                tran = _conn.BeginTransaction();


                SQLiteCommand cmd = new SQLiteCommand(findQuery, _conn, tran);
                cmd.Parameters.AddWithValue("@name", userName);

                IDataReader rdr = cmd.ExecuteReader();
                if (rdr.Read())
                {
                    return new User { ID = (int)(long)rdr["ID"], Name = (string)rdr["Name"] };
                }
                rdr.Close();


                cmd = new SQLiteCommand(insertQuery, _conn, tran);
                cmd.Parameters.AddWithValue("@name", userName);
                cmd.ExecuteNonQuery();

                cmd = new SQLiteCommand(getLastGeneratedID, _conn, tran);
                long lastId = (long)cmd.ExecuteScalar();


                tran.Commit();
                return new User() { ID = (int)lastId, Name = userName };

            }
            catch (SQLiteException)
            {
                tran.Rollback();
                throw;
            }

      

          
        }


        public void Add(GuestBookEntry guestBookEntry)
        {
            User user = AddUser(guestBookEntry.UserName);
           
            string insertQuery = "insert into messages(userid, text) values(@userid, @text)";
    
            SQLiteCommand cmd = new SQLiteCommand(insertQuery, _conn);

            cmd.Parameters.AddWithValue("@userid", user.ID);
            cmd.Parameters.AddWithValue("@text", guestBookEntry.Message);
            cmd.ExecuteNonQuery();
        }

        public IEnumerable<GuestBookEntry> FindAll()
        {
            string selectQuery =
                "select text, name from Messages m inner join Users u on m.UserID = u.ID order by u.ID asc";

            SQLiteCommand cmd = new SQLiteCommand(selectQuery, _conn);


            List<GuestBookEntry> entries = new List<GuestBookEntry>();

            using (IDataReader rdr = cmd.ExecuteReader())
            {

                while (rdr.Read())
                {
                    string user = (string) rdr["name"];
                    string message = (string) rdr["text"];

                    entries.Add(new GuestBookEntry(user, message));

                }
            }

            return entries;
        }


        
        ~SqlLiteGuestBook()  
        {
           Dispose(false);
        }

        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }


        private void Dispose(bool disposing)
        {

            if (!_disposed)
            {

                if (disposing)
                {
                    // dispose managed resources
                    _conn.Close();
                }

                //unmanaged resources...
            }
            _disposed = true;
        }


        class User
        {
            public int ID { get; set; }
            public string Name { get; set; }
        }
    }
}
